-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: 普通表，驱动表含唯一索引，结合临时表

--step1：创建测试表与索引; expect：成功
drop table if exists t_ustore_dml_orderby_031_01;
create temporary table t_ustore_dml_orderby_031_01
(c_id int,c_c_id int not null,c_w_id int not null,c_first varchar(16) not null,c_middle char(2),
 c_last varchar(16) not null,c_street_1 varchar(20) not null,c_street_2 varchar(20),
 c_city varchar(20) not null,c_state char(2) not null,c_zip char(9) not null,
 c_phone char(16) not null,c_since timestamp,c_credit char(2) not null,c_credit_lim numeric(12,2),
 c_discount numeric(4,4),c_balance numeric(12,2),c_ytd_payment real not null,c_payment_cnt number not null,
 c_delivery_cnt bool not null,c_end date not null,c_data date,c_text blob,c_clob clob
 ) with (storage_type=ustore);
drop index if exists i_ustore_dml_orderby_031_01_1;
create index i_ustore_dml_orderby_031_01_1 on t_ustore_dml_orderby_031_01(c_id,c_w_id);
drop index if exists i_ustore_dml_orderby_031_01_2;
create index i_ustore_dml_orderby_031_01_2 on t_ustore_dml_orderby_031_01(c_first,c_data);
drop table if exists t_ustore_dml_orderby_031_02 cascade;
create table t_ustore_dml_orderby_031_02(
     col_1 integer,
     col_2 bigint constraint t_ustore_dml_orderby_031_02_pk primary key,
     col_3 double precision,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 date,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step2：插入数据; expect：成功
drop sequence if exists seq_ustore_dml_orderby;
create sequence seq_ustore_dml_orderby increment by 1 start with 10;
insert into t_ustore_dml_orderby_031_02 values(1,seq_ustore_dml_orderby.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_031_02 values(1,seq_ustore_dml_orderby.nextval, 1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','5','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_031_02 values(2,seq_ustore_dml_orderby.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_031_02 values(3,seq_ustore_dml_orderby.nextval,   1+445.255,98*0.99, false,lpad('abc','4','@'),lpad('abc','4','b'),rpad('abc','6','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_031_02 values(3,seq_ustore_dml_orderby.nextval,   1+445.255,98*0.99, false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(INTERVAL '4 5:07:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_031_02 values(4,seq_ustore_dml_orderby.nextval,   1+445.255,98*0.99,  true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(INTERVAL '4 5:07:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');

--step3：普通表，驱动表含唯一索引，结合临时表; expect：成功
select t1.col_1 from t_ustore_dml_orderby_031_01 t2 join t_ustore_dml_orderby_031_02 t1 on t1.col_1=t2.c_id where t2.c_id=2 order by t1.col_1;

--step4：清理环境; except：成功
drop sequence if exists seq_ustore_dml_orderby;
drop index i_ustore_dml_orderby_031_01_1;
drop index i_ustore_dml_orderby_031_01_2;
drop table t_ustore_dml_orderby_031_01;
drop table t_ustore_dml_orderby_031_02;